﻿#region Librerias
using System;
using System.Collections.Generic;
using System.Web;
using System.Data;
#endregion

/// <summary>
/// Class ClientCourse, define los cursos para un determinado cliente
/// </summary>

#region Class

public class clsClientCourse : DAC
{
    #region Variables

    private int idClientCourse, idClient, idCourse;
    private const String tabla = "dbo.tblClientCourse";

    #endregion

    #region Builder

    /// <summary>
    /// Constructor
    /// </summary>
    public clsClientCourse() { }

    #endregion

    #region Methods

    #region Consultations

    public DataSet SeeAllTheRecords()
    {
        String query = "SELECT row_number() over(order by " + tabla + ".idClientCourse) As NUMBER, ";
        query += tabla + ".idClientCourse As ID, " + tabla + ".idClient As CLIENT, " + tabla + ".idCourse As COURSE ";
        query += "FROM " + tabla;
        return this.ExecuteQuery(query);
    }

    public DataSet SeeAllTheRecords(int startRecord, int maxRecord, String text, string idClient)
    {

        String query = "SELECT row_number() over(order by " + tabla + ".idClientCourse) As NUMBER, ";
        query += tabla + ".idClientCourse As ID, " + tabla + ".idClient As CLIENT, " + tabla + ".idCourse As COURSE, dbo.tblCourse.sName As NAMECOURSE, ";
        query += "dbo.tblCourse.sUrlDoc As URLDOC ";
        query += "FROM " + tabla + ", dbo.tblCourse ";
        query += "WHERE " + tabla + ".idClient =" + idClient;
        query += " AND " + tabla + ".idCourse = dbo.tblCourse.idCourse";
        
        if (!text.Equals(""))
        {
            query += " AND (dbo.tblCourse.sName LIKE '%" + text + "%' ";
            query += " OR dbo.tblCourse.sDescription LIKE '%" + text + "%') ";
        }
        if (startRecord == 0 && maxRecord == 0)
        {
            return this.ExecuteQuery(query);
        }
        else
        {
            return this.ExecuteQuery(query, startRecord, maxRecord);
        }
    }

    public DataSet ConsultID(String id)
    {
        String query = "SELECT row_number() over(order by " + tabla + ".idClientCourse) As NUMBER, ";
        query += tabla + ".idClientCourse As ID, " + tabla + ".idClient As CLIENT, " + tabla + ".idCourse As COURSE ";
        query += "FROM " + tabla;
        query += " WHERE " + tabla + ".idClientCourse =" + id;
        return this.ExecuteQuery(query);
    }

    public DataSet ConsultClientID(String idClient)
    {
        String query = "SELECT row_number() over(order by " + tabla + ".idClientCourse) As NUMBER, ";
        query += tabla + ".idClientCourse As ID, " + tabla + ".idClient As CLIENT, " + tabla + ".idCourse As COURSE ";
        query += "FROM " + tabla;
        query += " WHERE " + tabla + ".idClient =" + idClient;
        return this.ExecuteQuery(query);
    }

    public DataSet ConsultCourseID(String idCourse)
    {
        String query = "SELECT ";
        query += tabla + ".idClientCourse As ID, " + tabla + ".idClient As CLIENT, " + tabla + ".idCourse As COURSE ";
        query += "FROM " + tabla;
        query += " WHERE " + tabla + ".idCourse =" + idCourse;
        return this.ExecuteQuery(query);
    }

    public DataSet ConsultClientCourseIDCLIENT(String idClient)
    {
        String query = "SELECT row_number() over(order by " + tabla + ".idClientCourse) As NUMBER, ";
        query += tabla + ".idClientCourse As ID, " + tabla + ".idClient As CLIENT, " + tabla + ".idCourse As COURSE, dbo.tblCourse.sName As NAMECOURSE ";
        query += "FROM " + tabla + ", dbo.tblCourse ";
        query += "WHERE " + tabla + ".idClient =" + idClient;
        query += " AND " + tabla + ".idCourse = dbo.tblCourse.idCourse";
        return this.ExecuteQuery(query);
    }

    

    #endregion

    public int Insert()
    {
        String query = "INSERT INTO " + tabla;
        query += "(idClient, idCourse) ";
        query += "VALUES ('{0}','{1}')";

        String sql = String.Format(query, this.IdClient, this.IdCourse);
        return this.ExecuteDML(sql, "no");
    }

    public int Update()
    {
        String query = "UPDATE " + tabla + " SET ";
        query += tabla + ".idClient=" + this.IdClient + ", " + tabla + ".idCourse=" + this.IdCourse;
        query += " WHERE ";
        query += tabla + ".idClientCourse=" + this.ID;
        return this.ExecuteDML(query, "no");
    }

    public int Delete()
    {
        String query = "DELETE FROM " + tabla + " WHERE " + tabla + ".idClientCourse={0}";
        String sql = String.Format(query, this.ID);
        return this.ExecuteDML(sql,"no");
    }
    
    public int DeleteidClient_idCourse()
    {
        String query = "DELETE FROM " + tabla + " WHERE " + tabla + ".idClient={0} ";
        query += "AND " + tabla + ".idCourse={1}";
        String sql = String.Format(query, this.idClient, this.idCourse);
        return this.ExecuteDML(sql, "no");
    }

    #endregion

    #region Setters and Getters

    public int IdCourse
    {
        get { return idCourse; }
        set { idCourse = value; }
    }

    public int IdClient
    {
        get { return idClient; }
        set { idClient = value; }
    }

    public int ID
    {
        get { return idClientCourse; }
        set { idClientCourse = value; }
    }

    #endregion

}

#endregion